﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.ComponentModel;

namespace www.serviciipeweb.ro.CDC
{
    static class SQLHelperLSN
    {
        public static object Null2DBNull(object o)
        {
            if (o != null)
                return o;
            return DBNull.Value;
        }
        public static byte[] LSN_END(DateTime dt,string DBName)
        {

            using (SqlConnection scon = new SqlConnection())
            {
                scon.ConnectionString = SQLHelperLSN.SqlConnection;
                scon.Open();
                using (SqlCommand sc = new SqlCommand())
                {
                    sc.CommandType = System.Data.CommandType.Text;
                    sc.CommandText = "select " + DBName + @".sys.fn_cdc_map_time_to_lsn(
                'largest less than or equal', 
                 convert(datetime,'" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "',120))";
                    sc.Connection = scon;

                    object o = sc.ExecuteScalar();
                    //console.writeline(sc.CommandText);
                    //console.writeline("end : " + (o != null && o != DBNull.Value));                

                    return o as byte[];

                }
            }

        }
        public static byte[] LSN_Start(DateTime dt,string DBName)
        {
            using (SqlConnection scon = new SqlConnection())
            {
                scon.ConnectionString = SQLHelperLSN.SqlConnection;
                scon.Open();
                using (SqlCommand sc = new SqlCommand())
                {
                    sc.CommandType = System.Data.CommandType.Text;
                    sc.CommandText = "select " + DBName + @".sys.fn_cdc_map_time_to_lsn(
                'smallest greater than or equal', 
                convert(datetime,'" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "',120))";
                    //console.writeline(sc.CommandText);
                    sc.Connection = scon;
                    object o = sc.ExecuteScalar();
                    //console.writeline(sc.CommandText);
                    //console.writeline("Start : " + (o != null && o != DBNull.Value));
                    return o as byte[];

                }
            }
            
        }
        public static void ExecuteNonQuery(string Text)
        {
            using (SqlConnection scon = new SqlConnection())
            {
                scon.ConnectionString = SQLHelperLSN.SqlConnection;
                scon.Open();
                using (SqlCommand sc = new SqlCommand())
                {
                    sc.CommandType = System.Data.CommandType.Text;
                    sc.CommandText = Text;
                    sc.Connection = scon;
                    sc.ExecuteNonQuery();
                }
            }
        }
        public static object ExecuteScalar(string Text)
        {
            using (SqlConnection scon = new SqlConnection())
            {
                scon.ConnectionString = SQLHelperLSN.SqlConnection;
                scon.Open();
                using (SqlCommand sc = new SqlCommand())
                {
                    sc.CommandType = System.Data.CommandType.Text;
                    sc.CommandText = Text;
                    sc.Connection = scon;
                    return sc.ExecuteScalar();
                }
            }
        }
        public static string SqlConnection;
        public static DateTime? LSN2Time(byte[] LSN, string DBName)
        {
            
            using (SqlConnection scon = new SqlConnection())
            {
                scon.ConnectionString = SqlConnection;
                scon.Open();
                using (SqlCommand sc = new SqlCommand())
                {
                    sc.CommandType = System.Data.CommandType.Text;
                    sc.CommandText = "use " + DBName + "; select sys.fn_cdc_map_lsn_to_time(@lsn)";
                    sc.Connection = scon;
                    sc.Parameters.AddWithValue("lsn", LSN);


                    ////console.writeline(sc.CommandText);
                    object o = sc.ExecuteScalar();
                    ////console.writeline("First cp:" + ((o == null || o == DBNull.Value)));
                    if (o == null || o == DBNull.Value)
                        return null;
                    ////console.writeline("exec first capture" + o.GetType().ToString());
                    return SqlUtilities<DateTime>.GetValueOrDefault(o);
                }
            }
        }

    }
    /// <summary>
    /// TODO : maybe implement other ways this utilities
    /// </summary>
    /// <typeparam name="T"></typeparam>
    static class SqlUtilities<T>
    {
        
        

        public static string FormatSQL(T p)
        {
            if(typeof(T) == typeof(string))
                return "'" + p.ToString().Replace("'", "''") + "'";

            return p.ToString();
        }
        public static T GetValueOrDefault(SqlDataReader sr, string ColumnName)
        {
            return GetValueOrDefault(sr[ColumnName]);
        }
        
        /// <summary>
        /// http://www.hanselman.com/blog/TypeConvertersTheresNotEnoughTypeDescripterGetConverterInTheWorld.aspx
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="mystring"></param>
        /// <returns></returns>
        public static T GetTfromString(string mystring)
        {
            var f = TypeDescriptor.GetConverter(typeof(T));
            return (T)(f.ConvertFromInvariantString(mystring));
        }  
        public static T GetValueOrDefault(object o)
        {
            if (o == null)
                return default(T);

            if (o == DBNull.Value)
                return default(T);

            if (typeof(T).IsClass)
                return (T)o ;
            else//value type
                return (T)Convert.ChangeType(o.ToString(), typeof(T));
        }
    }
}
